package com.ziyun.erp.modules.freightBase.service.impl;

import com.ziyun.erp.modules.freightages.service.FreightAreaService;
import io.renren.common.exception.RRException;
import io.renren.common.utils.StringUtil;
import org.apache.poi.ss.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.math.BigDecimal;
import java.util.*;

import com.baomidou.mybatisplus.mapper.EntityWrapper;
import com.baomidou.mybatisplus.plugins.Page;
import com.baomidou.mybatisplus.service.impl.ServiceImpl;
import io.renren.common.utils.PageUtils;
import io.renren.common.utils.Query;

import com.ziyun.erp.modules.freightBase.dao.FreightBaseDao;
import com.ziyun.erp.modules.freightBase.entity.FreightBaseEntity;
import com.ziyun.erp.modules.freightBase.service.FreightBaseService;
import org.springframework.web.multipart.MultipartFile;


@Service("freightBaseService")
public class FreightBaseServiceImpl extends ServiceImpl<FreightBaseDao, FreightBaseEntity> implements FreightBaseService {
    @Autowired
    private FreightAreaService freightAreaService;

    @Override
    public PageUtils queryPage(Map<String, Object> params) {
        String regionNameStart = params.get("regionNameStart") == null? null : params.get("regionNameStart").toString();
        String regionNameEnd = params.get("regionNameEnd") == null? null : params.get("regionNameEnd").toString();
        Page<FreightBaseEntity> page = this.selectPage(
                new Query<FreightBaseEntity>(params).getPage(),
                new EntityWrapper<FreightBaseEntity>()
                .like(StringUtil.isNotEmpty(regionNameStart),"region_name_start",  regionNameStart)
                .like(StringUtil.isNotEmpty(regionNameEnd),"region_name_end",  regionNameEnd)
        );
        return new PageUtils(page);
    }

    @Override
    public void handleExcel(MultipartFile multipartFile, String userName) {

        //处理excel 数据
        List<Map<String,Object>> mapList = this.excelToList(multipartFile);
        // 获取数据对应code
        List<Map<String,Object>> cityCodeList = this.cityList(mapList);
        // 组装提交数据
        List<FreightBaseEntity> freightBaseList = this.handleData(mapList,cityCodeList, userName);
        // 判断 新增或者修改
        Date date = new Date();
        List<FreightBaseEntity> updateFreightBaseList = this.baseMapper.selectListByList(freightBaseList);
        if (updateFreightBaseList.size() >0){
            for (FreightBaseEntity freightBaseEntity : updateFreightBaseList){
                FreightBaseEntity temp = this.getFreignBase(freightBaseEntity,freightBaseList);
                freightBaseEntity.setStartPrice(temp.getStartPrice());
                freightBaseEntity.setStepPrice(temp.getStepPrice());
                freightBaseEntity.setUpdateTime(date);
                freightBaseEntity.setUpdateName(userName);
                // 同时移除 该条数据
                freightBaseList.remove(temp);
            }
            //批量执行update
            this.updateBatchById(updateFreightBaseList);
        }
        // 批量执行插入
        if (freightBaseList.size() >0){
            this.insertBatch(freightBaseList);
        }
    }


    private List<Map<String,Object>> excelToList(MultipartFile multipartFile){
        List<Map<String,Object>> mapList = new ArrayList<>();
        try{
            Workbook wb  = WorkbookFactory.create(multipartFile.getInputStream());
            // 获取第一个sheet
            Sheet sheet = wb.getSheetAt(0);
            // 从第三行开始读取数据
            for(int i=3; i<sheet.getLastRowNum()+1;i++){
                Row row = sheet.getRow(i);
                //循环列
                for(int j=0;j<row.getLastCellNum(); j=j+2){
                    // 前两列为起城市
                    Cell startCity = row.getCell(0);
                    if (startCity==null ||  startCity.getCellType() ==Cell.CELL_TYPE_BLANK){
                        throw new RRException("第" +(i+1) +"行第一列不能为空!");
                    }
                    Cell startChildCity  = row.getCell(1);
                    if (startChildCity == null ||  startChildCity.getCellType() ==Cell.CELL_TYPE_BLANK){
                        throw new RRException("第" +(i+1) +"行第二列不能为空!");
                    }
                    if (j>1){
                        //首重
                        Cell sz = row.getCell(j);
                        if (sz == null ||  sz.getCellType() == Cell.CELL_TYPE_BLANK){
                            throw new RRException("第" +(i+1) +"行 第"+(j+1)+"列不能为空!");
                        }
                        // 续重
                        Cell xz = row.getCell(j+1);
                        if (xz == null ||  xz.getCellType() == Cell.CELL_TYPE_BLANK){
                            throw new RRException("第" +(i+1) +"行 第"+(j+2)+"列不能为空!");
                        }
                        // 结束城市
                        Cell endCity = sheet.getRow(0).getCell(row.getCell(j).getColumnIndex());
                        if (endCity == null ||  endCity.getCellType()  == Cell.CELL_TYPE_BLANK){
                            throw new RRException("第一行 第"+(j+1)+"列不能为空!");
                        }
                        Cell endChildCity = sheet.getRow(1).getCell(row.getCell(j).getColumnIndex());
                        if (endChildCity == null ||  endChildCity.getCellType() == Cell.CELL_TYPE_BLANK){
                            throw new RRException("第二行 第"+(j+1)+"列不能为空!");
                        }
                        Map map = new HashMap();
                        map.put("startCity",startCity.getStringCellValue());
                        map.put("startChildCity",startChildCity.getStringCellValue());
                        map.put("sz",sz.getNumericCellValue());
                        map.put("xz",xz.getNumericCellValue());
                        map.put("endCity",endCity.getStringCellValue());
                        map.put("endChildCity",endChildCity.getStringCellValue());
                        mapList.add(map);
                    }
                }
            }
        }catch (Exception e){
            throw new RRException("导入Excel异常" +e.getMessage());
        }

        return  mapList;
    }

    private List<Map<String,Object>> cityList(List<Map<String,Object>> mapList ){
        Set<String> setCol = new TreeSet<>();
        // 去重
        for(Map map :mapList ){
            String startCity = map.get("startCity").toString();
            String startChildCity = map.get("startChildCity").toString();
            setCol.add(new StringBuilder(startCity).append(",").append(startChildCity).toString());
            String endCity = map.get("endCity").toString();
            String endChildCity = map.get("endChildCity").toString();
            setCol.add(new StringBuilder(endCity).append(",").append(endChildCity).toString());
        }
        // 循环查询
        List<Map<String,Object>> listCityCode = new ArrayList<>();
        for(String city : setCol){
            Map<String, Object> mapCityCode = new HashMap<>();
            String[] citys = city.split(",");
            Integer regionId = this.freightAreaService.getRegionId(citys[0],citys[1]);
            if(regionId == null){
                throw new RRException(city +"未配置地址!");
            }
            mapCityCode.put("city", city);
            mapCityCode.put("regionId",regionId);
            listCityCode.add(mapCityCode);
        }
        return  listCityCode;
    }

    private List<FreightBaseEntity> handleData(List<Map<String,Object>> mapList,List<Map<String,Object>> cityCodeList,String userName){
        // 组装数据 插入模板
        List<FreightBaseEntity> freightBaseList = new ArrayList<>();
        Date date = new Date();
        for (Map map : mapList){
            FreightBaseEntity freightBase = new FreightBaseEntity();
            String startCity = map.get("startCity").toString();
            String startChildCity = map.get("startChildCity").toString();
            String startCitys = new StringBuilder(startCity).append(",").append(startChildCity).toString();
            //起始区域ID
            Integer regionIdStart = this.getCityCode(startCitys,cityCodeList);
            if(regionIdStart == null){
                throw new RRException(startCitys+"未配置地址!");
            }
            String endCity = map.get("endCity").toString();
            String endChildCity = map.get("endChildCity").toString();
            String endCitys = new StringBuilder(endCity).append(",").append(endChildCity).toString();

            Integer regionIdEnd = this.getCityCode(endCitys,cityCodeList);
            if(regionIdEnd == null){
                throw new RRException(endCitys+"未配置地址!");
            }
            freightBase.setRegionIdStart(regionIdStart);
            freightBase.setRegionNameStart(startCitys);
            freightBase.setRegionIdEnd(regionIdEnd);
            freightBase.setRegionNameEnd(endCitys);
            freightBase.setStartPrice(BigDecimal.valueOf(Double.valueOf(map.get("sz").toString())));
            freightBase.setStepPrice(BigDecimal.valueOf(Double.valueOf(map.get("xz").toString())));
            freightBase.setCreateName(userName);
            freightBase.setCreateTime(date);
            freightBaseList.add(freightBase);
        }

        return  freightBaseList;
    }

    private Integer getCityCode(String citys, List<Map<String,Object>> cityCodeList){
        Integer code = null;
        for (Map map : cityCodeList){
            String city = map.get("city").toString();
            if (city.equals(citys)){
                code = Integer.parseInt(map.get("regionId").toString());
                break;
            }
        }
        return  code;
    }

    private FreightBaseEntity getFreignBase(FreightBaseEntity freightBaseEntity, List<FreightBaseEntity> freightBaseEntityList){
        FreightBaseEntity freightBase = new FreightBaseEntity();
        for (FreightBaseEntity temp : freightBaseEntityList){
            if (temp.getRegionIdStart().intValue() == freightBaseEntity.getRegionIdStart().intValue() &&
                    temp.getRegionIdEnd().intValue() == freightBaseEntity.getRegionIdEnd().intValue()  ){
                    freightBase = temp;
                    break;
            }
        }
        return  freightBase;
    }

}
